import pymysql      #导入pymysql数据库包
from PyQt5.QtWidgets import QApplication, QMainWindow,QWidget,QTableWidgetItem,QMessageBox  #导入需要的库和方法
from PyQt5.uic import loadUiType   #导入loadUiType方法，用于动态加载ui文件

'''
从ui定义中动态创建一个相应的窗口对象
里面的控件对象也就成为了窗口对象的属性了
比如：self.ui.button  ,self,ui,textEdit
动态加载ui文件的好处：实现了界面设计和功能代码的分离
'''

# UI--Logic 分离 动态加载UI文件
Denglu_Window, _ = loadUiType("denglu.ui")
zhuce_Window, _ = loadUiType("zhuce.ui")
main_Window, _ = loadUiType("main.ui")


###########################################################注册窗口#############################################################
class Zhuce_Ui(QWidget,zhuce_Window):
    def __init__(self):            #需要在类中定义init()方法，方便创建实例的时候，需要给实例绑定上属性，也方便类中的方法（函数）的定义
        QWidget.__init__(self)
        self.setupUi(self)
        self.button()

    def button(self):
        self.determine.clicked.connect(self.determine_zhuce)           #self.控件对象.connect(函数方法)
        self.return_denglu.clicked.connect(self.fanhui)                #使用connect方法连接按钮和功能
        self.clear_pushButton.clicked.connect(self.Reset)

    # 向用户表中添加用户的操作
    def determine_zhuce(self):
        # 使用pymysql.connect连接数据库
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()  # 创建游标
        sql = "insert into user_table(username,userpwd) values(%s,%s)"     # sql语句
        username = self.zhuce_username_Edit.text()         # 实现从指定界面输入框获取指定输入信息
        userpwd = self.zhuce_password_Edit.text()
        userpwd2 = self.zhuce_password_Edit2.text()
        user_info = (username, userpwd)
        if userpwd == userpwd2:                        # 如果密码和确认密码相同，则执行以下代码
            cur.execute(sql, user_info)                # 执行sql语句
            db.commit()  # 提交
            cur.close()
            db.close()
            QMessageBox().information(None, "提示", "用户注册成功", QMessageBox.Yes)
        else:
            warning = QMessageBox.warning(self, "两次密码不一致", "是否重新注册？", QMessageBox.Yes | QMessageBox.No)
            if warning == QMessageBox.Yes:
                self.zhuce_username_Edit.setText('')
                self.zhuce_password_Edit.setText('')
                self.zhuce_password_Edit2.setText('')

    # 返回函数，返回登录界面
    def fanhui(self):
        self.app = Denglu_Ui()
        self.close()
        self.app.show()

    # 重置函数，调用.clear()方法清除控件中的内容
    def Reset(self):
        self.zhuce_username_Edit.clear()
        self.zhuce_password_Edit.clear()
        self.zhuce_password_Edit2.clear()

###########################################################登录窗口#############################################################
class Denglu_Ui(QWidget,Denglu_Window):
    def __init__(self):
        QWidget.__init__(self)
        self.setupUi(self)
        self.button()


    def button(self):
        self.zhuce_Button.clicked.connect(self.zhuce)
        self.denglu_Button.clicked.connect(self.denglu)

    #跳转到注册界面
    def zhuce(self):
        self.app=Zhuce_Ui()
        self.close()
        self.app.show()

    # 跳转到主界面
    def denglu(self):
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")   #连接数据库
        cur = db.cursor()                        #定义游标
        sql = "select * from user_table where username=%s and userpwd=%s"       #sql语句
        a = self.user_name_Edit.text()         #.text()方法从相应的编辑框中获取输入的内容
        b = self.user_password_Edit.text()
        cur.execute(sql,(a, b))          # 使用execute方法传入参数，并执行sql语句
        data = cur.fetchone()            # 取其中一条数据
        if data:                         # 如果存在数据，则执行以下功能
            self.app =Main_Ui()          # 把mainAPP窗口赋值为app
            self.close()                 # 当前窗口关闭
            self.app.show()              # 主窗口打开
        else:
            warning = QMessageBox.warning(self, "登陆失败", "用户名或密码错误，是否重新登录？", QMessageBox.Yes | QMessageBox.No)
            if warning == QMessageBox.Yes:
                self.user_name_Edit.setText('')             #清空编辑框重新登录
                self.user_password_Edit.setText('')


###########################################################主窗口#############################################################
class Main_Ui(QMainWindow,main_Window):
    def __init__(self):
        QMainWindow.__init__(self)
        self.setupUi(self)
        self.button()
        self.hid()

    def button(self):
        # 控制QStackedWidget分页的四个按钮，即：四个管理界面界面的切换
        self.shangping_Button.clicked.connect(self.btn1_fun)
        self.jinhuo_Button.clicked.connect(self.btn2_fun)
        self.yuangong_Button.clicked.connect(self.btn3_fun)
        self.fenxi_Button.clicked.connect(self.btn4_fun)

        # 学生管理界面的按钮
        self.determine_add_goods_info_Button.clicked.connect(self.determine_add_goods_info)   # 添加学生信息
        self.pushButton_4.clicked.connect(self.re_goods_info)                                 # 修改学生信息
        self.queren_delete_goods_Button.clicked.connect(self.delete_goods_info)               # 修改学生信息
        self.queren_chakan.clicked.connect(self.determine_cha_goods_info)                     # 修改学生信息

        # 班级界面管理的按钮
        self.chakan_factory_Button.clicked.connect(self.show_factory_info)
        self.chakan_factory_Button1.clicked.connect(self.show_factory_info1)
        self.delete_factory_Button.clicked.connect(self.delete_factory_info)
        self.re_factory_Button.clicked.connect(self.re_factory_info)
        self.add_factory_Button.clicked.connect(self.add_factory_info)

        # 员工管理界面的按钮，使用了tabWidget控件分页操作
        self.chakan_staff.clicked.connect(self.chakan_staff_tab)
        self.add_staff.clicked.connect(self.add_staff_tab)
        self.re_staff.clicked.connect(self.re_staff_tab)
        self.delete_staff.clicked.connect(self.delete_staff_tab)

        # 员工管理界面的按钮，对该管理界面进行各项操作的按钮
        self.show_staff_Button.clicked.connect(self.determine_cha_staff_info)
        self.show_staff_Button2.clicked.connect(self.determine_cha_staff_info2)
        self.determine_add_staff.clicked.connect(self.determine_add_staff_info)
        self.show_staff_Button3.clicked.connect(self.determine_cha_staff_info3)
        self.re_staff_Button.clicked.connect(self.re_staff_info)
        self.show_staff_Button4.clicked.connect(self.determine_cha_staff_info4)
        self.detete_staff_Button.clicked.connect(self.delete_staff_info)

        # 最后一页汇总统计的按钮
        self.Button_1.clicked.connect(self.show_total_info)
        self.Button_2.clicked.connect(self.add_other_goods_cost)
        self.Button_3.clicked.connect(self.goods_total)
        self.Button_4.clicked.connect(self.show_total_staff)
        self.Button_5.clicked.connect(self.staff_total)
        self.Button_6.clicked.connect(self.total_1)
        self.Button_7.clicked.connect(self.add_other_cost)

    # QStackedWidget(做堆载窗口控件), 可以添加多个page, 每个page显示不同的内容
    # 页面中使用这个功能，可以再共同一区域在不同条件下显示不同内容，方便开发使用
    # 最外层分页的功能实现函数
    def btn1_fun(self):
        self.stackedWidget.setCurrentIndex(0)
    def btn2_fun(self):
        self.stackedWidget.setCurrentIndex(1)
    def btn3_fun(self):
        self.stackedWidget.setCurrentIndex(2)
    def btn4_fun(self):
        self.stackedWidget.setCurrentIndex(3)


#################################################### 班级管理页 ##############################################################
    # 添加班级功能
    def determine_add_goods_info(self):
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()              # 创建游标
        sql = "insert into goods_info (goods_name,goods_brand,goods_material,goods_price) values(%s,%s,%s,%s)"    # sql语句
        goods_name1 = self.add_goods_name.text()         # 实现从指定界面输入框获取指定输入信息
        goods_brand2 = self.add_goods_brand.text()
        goods_material3 = self.add_goods_material.text()
        goods_price4 = self.add_goods_price.text()
        goods_price_info = (goods_name1, goods_brand2,goods_material3,goods_price4)
        cur.execute(sql, goods_price_info)                # 执行sql语句
        db.commit()  # 提交
        cur.close()
        db.close()
        QMessageBox().information(None, "提示", "学生添加成功", QMessageBox.Yes)
        #清空编辑框，利用.setText()返回空值
        self.add_goods_name.setText('')
        self.add_goods_brand.setText('')
        self.add_goods_material.setText('')
        self.add_goods_price.setText('')

    # 修改学生功能
    def re_goods_info(self):
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()     # 创建游标
        sql = "update goods_info set goods_name=%s,goods_brand=%s,goods_material=%s,goods_price=%s where goods_name=%s"     # sql语句
        f = self.lineEdit_7.text()         # 实现从指定界面输入框获取指定输入信息
        a = self.lineEdit_8.text()
        b = self.lineEdit_9.text()
        c = self.lineEdit_10.text()
        d = self.lineEdit_11.text()
        cur.execute(sql, (a,b,c,d,f))                # 执行sql语句
        db.commit()   # 提交
        cur.close()
        db.close()
        QMessageBox().information(None, "提示", "学生修改成功", QMessageBox.Yes)
        #清空编辑框，利用.setText()返回空值
        self.lineEdit_7.setText('')
        self.lineEdit_8.setText('')
        self.lineEdit_9.setText('')
        self.lineEdit_10.setText('')
        self.lineEdit_11.setText('')

    # 删除学生功能
    def delete_goods_info(self):
        a = self.delete_goods.text()  # 实现从指定界面输入框获取指定输入信息
        warning = QMessageBox.warning(self, "删除", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
        if warning == QMessageBox.Yes:
            db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
            cur = db.cursor()        # 创建游标
            sql = "delete from goods_info where goods_name=%s"       # sql语句
            cur.execute(sql, a)                # 执行sql语句
            db.commit()   # 提交
            cur.close()
            db.close()
            QMessageBox().information(None, "提示", "学生删除成功", QMessageBox.Yes)
            #清空编辑框，利用.setText()返回空值
            self.delete_goods.setText('')

    # 查看学生功能
    def determine_cha_goods_info(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()           # 创建游标
        sql = "select * from goods_info where goods_name=%s"
        goods_name1 = self.goods_name1_Edit.text()
        cur.execute(sql, goods_name1)
        data = cur.fetchone()
        if data:
            self.show_goods_name.setText(str(data[1]))
            self.show_goods_brand.setText(str(data[2]))
            self.show_goods_material.setText(str(data[3]))
            self.show_goods_price.setText(str(data[4]))
        else:
            QMessageBox().information(None, "提示", "没有该学生", QMessageBox.Yes)

####################################################进货管理页##############################################################
    # 显示学生信息功能
    def show_factory_info(self):
        self.tableWidget_2.clear()
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()     # 创建游标
        sql = "select * from factory_table" # sql语句
        cur.execute(sql)                # 执行sql语句
        # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位
        data = cur.fetchall()
        if data:
            # 遍历二维元组, 将 id 和 name 显示到界面表格上
            x = 0
            for i in data:
                y = 0
                for j in i:
                    self.tableWidget_2.setItem(x, y, QTableWidgetItem(str(data[x][y])))
                    y = y + 1
                x = x + 1
        cur.close()
        db.close()

    def show_factory_info1(self):
        self.tableWidget_3.clear()
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()     # 创建游标
        sql = "select * from factory_table where factory_name=%s"         # sql语句
        a = self.lineEdit_18.text()
        cur.execute(sql,(a))                # 执行sql语句
        # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位
        data = cur.fetchall()
        if data:
            # 遍历二维元组, 将 id 和 name 显示到界面表格上
            x = 0
            for i in data:
                y = 0
                for j in i:
                    self.tableWidget_3.setItem(x, y, QTableWidgetItem(str(data[x][y])))
                    y = y + 1
                x = x + 1
            cur.close()
            db.close()
        else:
            QMessageBox().information(None, "提示", "学生不存在", QMessageBox.Yes)

    # 删除学生信息功能
    def delete_factory_info(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()  # 创建游标
        sql = "select * from factory_table where factory_name=%s"  # sql语句
        a = self.lineEdit_18.text()
        cur.execute(sql, a)  # 执行sql语句
        # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位
        data = cur.fetchall()
        if data:
            warning = QMessageBox.warning(self, "删除", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
            if warning == QMessageBox.Yes:
                sql = "delete from factory_table where factory_name=%s"       # sql语句
                cur.execute(sql, a)                # 执行sql语句
                db.commit()   # 提交
                QMessageBox().information(None, "提示", "学生信息删除成功", QMessageBox.Yes)
                cur.close()
                db.close()
        else:
            QMessageBox().information(None, "提示", "学生不存在", QMessageBox.Yes)
            cur.close()
            db.close()

    # 修改学生信息功能
    def re_factory_info(self):
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()     # 创建游标
        sql = "update factory_table set factory_name=%s,factory_address=%s,factory_phone=%s,factory_goods_name=%s,factory_num=%s, factory_price=%s where factory_name=%s" # sql语句
        f = self.lineEdit_18.text()         # 实现从指定界面输入框获取指定输入信息
        a = self.lineEdit_40.text()
        b = self.lineEdit_41.text()
        c = self.lineEdit_42.text()
        d = self.lineEdit_43.text()
        g = self.lineEdit_50.text()
        e = self.lineEdit_44.text()
        cur.execute(sql, (a,b,c,d,g,e,f))                # 执行sql语句
        db.commit()   # 提交
        cur.close()
        db.close()
        QMessageBox().information(None, "提示", "学生信息修改成功", QMessageBox.Yes)
        #清空编辑框，利用.setText()返回空值
        self.lineEdit_40.setText('')
        self.lineEdit_41.setText('')
        self.lineEdit_42.setText('')
        self.lineEdit_43.setText('')
        self.lineEdit_50.setText('')
        self.lineEdit_44.setText('')

    # 添加学生信息功能
    def add_factory_info(self):
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()              # 创建游标
        sql = "insert into factory_table (factory_name,factory_address,factory_phone,factory_goods_name,factory_num,factory_price) values(%s,%s,%s,%s,%s,%s)"    # sql语句
        a = self.lineEdit_45.text()         # 实现从指定界面输入框获取指定输入信息
        b = self.lineEdit_46.text()
        c = self.lineEdit_47.text()
        d = self.lineEdit_48.text()
        e = self.lineEdit_51.text()
        g = self.lineEdit_49.text()
        cur.execute(sql, (a,b,c,d,e,g))                # 执行sql语句
        db.commit()  # 提交
        cur.close()
        db.close()
        QMessageBox().information(None, "提示", "学生添加成功", QMessageBox.Yes)
        #清空编辑框，利用.setText()返回空值
        self.lineEdit_45.setText('')
        self.lineEdit_46.setText('')
        self.lineEdit_47.setText('')
        self.lineEdit_48.setText('')
        self.lineEdit_51.setText('')
        self.lineEdit_49.setText('')

####################################################员工管理页##############################################################
    # 将tabWidget的表头隐藏，而使用上方的四个切换按钮进行操作
    def hid(self):
        self.tabWidget_2.tabBar().setVisible(False)       # 隐藏tab 把最上面那个容器隐藏
    def chakan_staff_tab(self):
        self.tabWidget_2.setCurrentIndex(0)               # 索引为0
    def add_staff_tab(self):
        self.tabWidget_2.setCurrentIndex(1)
    def re_staff_tab(self):
        self.tabWidget_2.setCurrentIndex(2)               # 索引为1
    def delete_staff_tab(self):
        self.tabWidget_2.setCurrentIndex(3)

    # 查看员工信息功能
    def determine_cha_staff_info(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()           # 创建游标
        sql = "select * from staff_table where staff_name=%s"
        a = self.show_staff_Edit.text()
        cur.execute(sql, a)
        data = cur.fetchone()
        if data:
            self.show_staff_id_Edit.setText(str(data[0]))
            self.show_staff_name_Edit.setText(str(data[1]))
            self.show_staff_depart_Edit.setText(str(data[2]))
            self.show_staff_time_Edit.setText(str(data[3]))
            self.show_staff_salary_Edit.setText(str(data[4]))
        else:
            QMessageBox().information(None, "提示", "没有该员工", QMessageBox.Yes)
    def determine_cha_staff_info2(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()                   # 创建游标
        sql = "select * from staff_table"   # sql语句
        cur.execute(sql)  # 执行sql语句
        # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位
        data = cur.fetchall()
        if data:
            # 遍历二维元组, 将 id 和 name 显示到界面表格上
            x = 0
            for i in data:
                y = 0
                for j in i:
                    self.tableWidget.setItem(x, y, QTableWidgetItem(str(data[x][y])))
                    y = y + 1
                x = x + 1
    def determine_cha_staff_info3(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()           # 创建游标
        sql = "select * from staff_table where staff_name=%s"
        a = self.show_staff_Edit3.text()
        cur.execute(sql, a)
        data = cur.fetchone()
        if data:
            self.show_staff_id_Edit1.setText(str(data[0]))
            self.show_staff_name_Edit1.setText(str(data[1]))
            self.show_staff_depart_Edit1.setText(str(data[2]))
            self.show_staff_time_Edit1.setText(str(data[3]))
            self.show_staff_salary_Edit1.setText(str(data[4]))
        else:
            QMessageBox().information(None, "提示", "没有该员工", QMessageBox.Yes)
    def determine_cha_staff_info4(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()           # 创建游标
        sql = "select * from staff_table where staff_name=%s"
        a = self.show_staff_Edit4.text()
        cur.execute(sql, a)
        data = cur.fetchone()
        if data:
            self.lineEdit_33.setText(str(data[0]))
            self.lineEdit_34.setText(str(data[1]))
            self.lineEdit_35.setText(str(data[2]))
            self.lineEdit_36.setText(str(data[3]))
            self.lineEdit_37.setText(str(data[4]))
        else:
            QMessageBox().information(None, "提示", "没有该员工", QMessageBox.Yes)

    # 添加员工信息功能
    def determine_add_staff_info(self):
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()              # 创建游标
        sql = "insert into staff_table (staff_id,staff_name,staff_depart,staff_time,staff_salary) values(%s,%s,%s,%s,%s)"    # sql语句
        a = self.add_staff_id_Edit.text()         # 实现从指定界面输入框获取指定输入信息
        b = self.add_staff_name_Edit.text()
        c = self.add_staff_depart_Edit.text()
        d = self.add_staff_time_Edit.text()
        e = self.add_staff_salary_Edit.text()
        cur.execute(sql, (a,b,c,d,e))                # 执行sql语句
        db.commit()  # 提交
        cur.close()
        db.close()
        QMessageBox().information(None, "提示", "员工添加成功", QMessageBox.Yes)
        #清空编辑框，利用.setText()返回空值
        self.add_staff_id_Edit.setText('')
        self.add_staff_name_Edit.setText('')
        self.add_staff_depart_Edit.setText('')
        self.add_staff_time_Edit.setText('')
        self.add_staff_salary_Edit.setText('')

    # 修改员工信息功能
    def re_staff_info(self):
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()     # 创建游标
        sql = "update staff_table set staff_id=%s,staff_name=%s,staff_depart=%s,staff_time=%s,staff_salary=%s where staff_name=%s" # sql语句
        f = self.show_staff_Edit3.text()         # 实现从指定界面输入框获取指定输入信息
        a = self.lineEdit_24.text()
        b = self.lineEdit_23.text()
        c = self.lineEdit_25.text()
        d = self.lineEdit_26.text()
        g = self.lineEdit_27.text()
        cur.execute(sql, (a,b,c,d,g,f))                # 执行sql语句
        db.commit()   # 提交
        cur.close()
        db.close()
        QMessageBox().information(None, "提示", "员工信息修改成功", QMessageBox.Yes)
        #清空编辑框，利用.setText()返回空值
        self.show_staff_Edit3.setText('')
        self.lineEdit_24.setText('')
        self.lineEdit_23.setText('')
        self.lineEdit_25.setText('')
        self.lineEdit_26.setText('')
        self.lineEdit_27.setText('')

    # 删除员工信息功能
    def delete_staff_info(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()  # 创建游标
        sql = "select * from staff_table where staff_name=%s"  # sql语句
        a = self.show_staff_Edit4.text()
        cur.execute(sql, a)  # 执行sql语句
        # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位
        data = cur.fetchall()
        if data:
            warning = QMessageBox.warning(self, "删除", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
            if warning == QMessageBox.Yes:
                sql1 = "delete from staff_table where staff_name=%s"       # sql语句
                b = self.show_staff_Edit4.text()
                cur.execute(sql1, b)                # 执行sql语句
                db.commit()   # 提交
                cur.close()
                db.close()
                QMessageBox().information(None, "提示", "员工信息删除成功", QMessageBox.Yes)
        else:
            QMessageBox().information(None, "提示", "员工不存在", QMessageBox.Yes)
            cur.close()
            db.close()

####################################################汇总统计页##############################################################

    def show_total_info(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()                   # 创建游标
        sql = "select goods_name,goods_price,factory_price,factory_num,goods_price*factory_num-factory_price*factory_num from goods_info,factory_table where goods_name=factory_goods_name"   # sql语句
        cur.execute(sql)  # 执行sql语句
        # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位
        data = cur.fetchall()
        if data:
            # 遍历二维元组, 将 id 和 name 显示到界面表格上
            x = 0
            for i in data:
                y = 0
                for j in i:
                    self.tableWidget_4.setItem(x, y, QTableWidgetItem(str(data[x][y])))
                    y = y + 1
                x = x + 1

    def add_other_goods_cost(self):
        db = pymysql.connect(host="localhost",user="root",passwd="Dqh0160108",database="homework",charset="utf8")
        cur = db.cursor()              # 创建游标
        sql = "insert into other_goods_cost (cost_name,cost1,cost2) values(%s,%s,%s)"    # sql语句
        a = self.cost_name.text()
        b = self.cost1_Edit.text()         # 实现从指定界面输入框获取指定输入信息
        c = self.cost2_Edit.text()
        cur.execute(sql, (a,b,c))                # 执行sql语句
        db.commit()  # 提交
        cur.close()
        db.close()
        QMessageBox().information(None, "提示", "添加成功", QMessageBox.Yes)
        #清空编辑框，利用.setText()返回空值
        self.cost1_Edit.setText('')
        self.cost2_Edit.setText('')
        self.cost_name.setText('')

    def goods_total(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()           # 创建游标
        sql = "select sum(goods_price * factory_num - factory_price * factory_num) - sum(cost1) - sum(cost2) from goods_info, factory_table, other_goods_cost where goods_name = factory_goods_name"
        cur.execute(sql)
        data = cur.fetchone()
        if data:
            self.goods_total_1.setText(str(data[0]))

    def show_total_staff(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()                   # 创建游标
        sql = "select staff_id,staff_name,staff_depart,staff_time,staff_salary,staff_time*staff_salary from staff_table  order by 6 desc "   # sql语句
        cur.execute(sql)  # 执行sql语句
        # 获取查询到的数据, 是以二维元组的形式存储的, 所以读取需要使用 data[i][j] 下标定位
        data = cur.fetchall()
        if data:
            # 遍历二维元组, 将 id 和 name 显示到界面表格上
            x = 0
            for i in data:
                y = 0
                for j in i:
                    self.tableWidget_5.setItem(x, y, QTableWidgetItem(str(data[x][y])))
                    y = y + 1
                x = x + 1

    def staff_total(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()           # 创建游标
        sql = "select sum(staff_time*staff_salary) from staff_table"
        cur.execute(sql)
        data = cur.fetchone()
        if data:
            self.staff_total_1.setText(str(data[0]))

    def add_other_cost(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()  # 创建游标
        sql = "insert into other_cost (cost3,cost4,cost5) values(%s,%s,%s)"  # sql语句
        a = self.cost_3.text()
        b = self.cost_4.text()  # 实现从指定界面输入框获取指定输入信息
        c = self.cost_5.text()
        cur.execute(sql, (a, b, c))  # 执行sql语句
        db.commit()  # 提交
        cur.close()
        db.close()
        QMessageBox().information(None, "提示", "添加成功", QMessageBox.Yes)
        # 清空编辑框，利用.setText()返回空值
        self.cost_3.setText('')
        self.cost_4.setText('')
        self.cost_5.setText('')

    def total_1(self):
        db = pymysql.connect(host="localhost", user="root", passwd="Dqh0160108", database="homework", charset="utf8")
        cur = db.cursor()           # 创建游标
        sql = "select sum(goods_price * factory_num - factory_price * factory_num) - sum(cost1) - sum(cost2)-sum(staff_time*staff_salary) from goods_info, factory_table, other_goods_cost, staff_table where goods_name = factory_goods_name"
        cur.execute(sql)
        data = cur.fetchone()
        if data:
            self.total_ok.setText(str(data[0]))



def main():
    app = QApplication([])     # 创建应用程序 创建一个QApplication，也就是你要开发的软件app
    window = Denglu_Ui()         # 创建主窗口  创建一个MainAPP，用来装载你需要的各种组件、控件
    window.show()              # 显示窗口 执行QMainWindow的show()方法，显示这个QMainWindow
    app.exec_()                # 使用exit()或者点击关闭按钮退出QApplication

if __name__ == '__main__':

    main()